package com.springboot.web.service;

import com.springboot.web.entity.Address;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.List;

@Service
public class AddressServiceImpl implements AddressService{

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Address> queryAll() {
        return jdbcTemplate.query("SELECT address_id,address,address2,district,city_id,postal_code,phone,ST_AsText(location) AS location,last_update from Address",
                (resultSet,i) ->
                {
                    Integer addressId = resultSet.getInt("address_id");
                    String address = resultSet.getString("address");
                    String address2 = resultSet.getString("address2");
                    String district = resultSet.getString("district");
                    Integer cityId = resultSet.getInt("city_id");
                    String postalCode = resultSet.getString("postal_code");
                    String phone = resultSet.getString("phone");
                    String location = resultSet.getString("location");
                    Timestamp lastUpdate = resultSet.getTimestamp("last_update");
                    return new Address(addressId,address,address2,district,cityId,postalCode,phone,location,lastUpdate);
                });
    }

    @Override
    public Address queryById(Integer id) {
        return jdbcTemplate.queryForObject("SELECT address_id,address,address2,district,city_id,postal_code,phone,ST_AsText(location) AS location,last_update from address where address_id = ?",
                (rs, rowNum) -> new Address(rs.getInt("address_id"),
                        rs.getString("address"),
                        rs.getString("address2"),
                        rs.getString("district"),
                        rs.getInt("city_id"),
                        rs.getString("postal_code"),
                        rs.getString("phone"),
                        rs.getString("location"),
                        rs.getDate("last_update"))
                , id);
    }

    @Override
    public void addOne(Address address) {
        String sql = "insert into address values(null,?,?,?,?,?,?,ST_GeomFromText(?),now());";
        jdbcTemplate.update(sql,
                address.getAddress(),
                address.getAddress2(),
                address.getDistrict(),
                address.getCityId(),
                address.getPostalCode(),
                address.getPhone(),
                address.getLocation());
    }

    @Override
    public void modify(Address address) {
        String update = "update address set address = ?,address2 = ?,district = ?,city_id = ?,postal_code = ?" +
                ",phone = ?,location = ST_GeomFromText(?),last_update = now() where address_id = ?";
        jdbcTemplate.update(update,
                address.getAddress(),
                address.getAddress2(),
                address.getDistrict(),
                address.getCityId(),
                address.getPostalCode(),
                address.getPhone(),
                address.getLocation(),
                address.getAddressId());
    }

    @Override
    public void deleteOne(Integer id) {
        String delete = "delete from address where address_id = ? ";
        jdbcTemplate.update(delete,id);
    }
}
